﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;

namespace Cocbook_DAL
{
     public class BookDAL
    {
         public bool CreateBook(Book b)
         {

             DB.SetQuery("INSERT INTO Book VALUES (@isbn, @title,@type,@publisher,@issuer,@size,@page,@price,@genre,@weigh)");
             DB.AddParameters("isbn", b.ISBN);
             DB.AddParameters("title", b.Title);
             DB.AddParameters("type", b.Type);
             DB.AddParameters("publisher", b.Publisher);
             DB.AddParameters("issuer", b.Issuer);
             DB.AddParameters("size", b.Size);
             DB.AddParameters("page", b.Page);
             DB.AddParameters("price", b.Price);
             DB.AddParameters("genre", b.Genre);
             DB.AddParameters("weigh", b.Weigh);
             try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.CreateBook", e.ToString());
                 return false;
             }
         }

         public bool UpdateBook(Book b)
         {
             DB.SetQuery("UPDATE Book SET ISBN=@isbn, Title=@title,Type=@type,Publisher=@publisher,Issuer=@issuer,Size=@size,Page=@page,Price=@price,Genre=@genre,Weigh=@weigh WHERE BookID=@id");
             DB.AddParameters("id", b.BookID);
             DB.AddParameters("isbn", b.ISBN);
             DB.AddParameters("title", b.Title);
             DB.AddParameters("type", b.Type);
             DB.AddParameters("publisher", b.Publisher);
             DB.AddParameters("issuer", b.Issuer);
             DB.AddParameters("size", b.Size);
             DB.AddParameters("page", b.Page);
             DB.AddParameters("price", b.Price);
             DB.AddParameters("genre", b.Genre);
             DB.AddParameters("weigh", b.Weigh);

             try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.UpdateBook", e.ToString());
                 return false;
             }
         }

         /// <summary>
         /// Lọc số lượng các đầu sách mà người dùng đã mua
         /// </summary>
         /// <param name="c"></param>
         /// <returns></returns>
         public List<Book_Filter> GetBookAndIndexByFilter(Customer c)
         {
             try
             {
                 DB.SetQuery(   "SELECT        TOP (100) PERCENT dbo.Book.BookID, SUM(dbo.Order_Book.Copy) AS Sold1, SUM(dbo.Bill_Book.Copy) AS Sold2            "+
                                "FROM            dbo.Bill INNER JOIN                                                                                            "+
                                "                dbo.Bill_Book ON dbo.Bill.BillID = dbo.Bill_Book.BillID RIGHT OUTER JOIN                                       "+
                                "                dbo.Book ON dbo.Bill_Book.BookID = dbo.Book.BookID LEFT OUTER JOIN                                             "+
                                "                dbo.[Order] INNER JOIN                                                                                         "+
                                "                dbo.Order_Book ON dbo.[Order].OrderID = dbo.Order_Book.OrderID ON dbo.Book.BookID = dbo.Order_Book.BookID      "+
                                "WHERE Book.BookID in                                                                                                           "+
                                "(                                                                                                                              "+
	                            "    SELECT        TOP (100) PERCENT dbo.Bill_Book.BookID                                                                       "+
	                            "    FROM            dbo.Bill_Book INNER JOIN                                                                                   "+
								"                                dbo.Bill ON dbo.Bill_Book.BillID = dbo.Bill.BillID INNER JOIN                                  "+
								"                                dbo.Customer ON dbo.Bill.CustomerID = dbo.Customer.CustomerID                                  "+
	                            "    WHERE        (dbo.Customer.CustomerID = @cid)                                                                              "+
	                            "    GROUP BY dbo.Bill_Book.BookID                                                                                              "+
                                "UNION                                                                                                                          "+
	                            "    SELECT        TOP (100) PERCENT dbo.Order_Book.BookID                                                                      "+
	                            "    FROM            dbo.[Order] INNER JOIN                                                                                     "+
							    "                             dbo.Customer ON dbo.[Order].CustomerID = dbo.Customer.CustomerID INNER JOIN                       "+
							    "                             dbo.Order_Book ON dbo.[Order].OrderID = dbo.Order_Book.OrderID                                    "+
	                            "    WHERE        (dbo.Customer.CustomerID = @cid)                                                                              "+
	                            "    GROUP BY dbo.Order_Book.BookID)                                                                                            "+
                                "AND  ([Order].CustomerID = @cid OR [Bill].CustomerID = @cid )                                                                "+
                                "GROUP BY dbo.Book.BookID");
                 DB.AddParameters("cid", c.CustomerID);
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<Book_Filter> result = new List<Book_Filter>();
                 Book_Filter bf = new Book_Filter();
                 int s1, s2;
                 if (llo.Count > 0)
                 {
                     foreach (List<object> lo in llo)
                     {
                         bf = new Book_Filter();
                         bf.Book = GetBookById(Convert.ToInt16(lo[0]));
                         if (lo[1] == System.DBNull.Value)
                         {
                             s1 = 0;
                         }
                         else
                         {
                             s1 = Convert.ToInt32(lo[1]);
                         }
                         if (lo[2] == System.DBNull.Value)
                         {
                             s2 = 0;
                         }
                         else
                         {
                             s2 = Convert.ToInt32(lo[2]);
                         }
                         bf.Sold = s1 + s2;
                         result.Add(bf);
                     }
                 }
                 return result;

             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetBookAndIndexByFilter", e.ToString());
                 return null;

             }
         }




         /// <summary>
         /// Lọc và tìm kiếm sách theo điều kiện, tính doanh thu và tổng hợp tác giả
         /// </summary>
         /// <param name="pID"> ProjectID </param>
         /// <param name="isbn"> Mã ISBN </param>
         /// <param name="publisher"> Nhà xuất bản </param>
         /// <param name="title"> Tên sách </param>
         /// <param name="author"> Tác giả </param>
         /// <param name="issuer"> Nhà phát hành </param>
         /// <param name="genre"> Thể loại </param>
         /// <param name="lowPrice"> Giá thấp nhất </param>
         /// <param name="highPrice"> Giá cao nhất </param>
         /// <param name="orderBy"> Sắp xếp theo (sold/price/name) </param>
         /// <param name="desc"> Sắp xếp tăng/giảm (false/true) </param>
         /// <returns></returns>
         public List<Book_FilterView> GetAllBookByFilter(int pID = 0, long isbn = 0, string publisher = "", string title = "", string author = "", string issuer = "", string genre = "", long lowPrice = 0, long highPrice = 0, string orderBy = "", bool desc = false)
         {
             try
             {
                 string sqlString = @"SELECT DISTINCT _b.BookID, _b.ISBN, _b.Title, _b.Type, _b.Publisher, _b.Issuer, ISNULL(_b.Size,'N/A'),ISNULL(_b.Page,'N/A'), _b.Price, _b.Genre, _b.Weigh, b.Authors, bookSold.Sold
	                                    FROM (SELECT        dbo.Book.BookID, ISNULL(dbo.Author.Name, 'N/A') AS authors
	                                            FROM            dbo.Author INNER JOIN
								                                dbo.Book_Author ON dbo.Author.AuthorID = dbo.Book_Author.AuthorID RIGHT OUTER JOIN
								                                dbo.Book ON dbo.Book_Author.BookID = dbo.Book.BookID) AS b, 
	                                    (SELECT S0.BookID, (ISNULL(S1.BillSold, 0) + ISNULL(S2.OrderSold, 0)) AS Sold
                                    FROM
	                                    ((SELECT DISTINCT pb.BookID 
		                                    FROM Project_Book pb
		                                    WHERE (pb.ProjectID = @projectID OR @projectID = 0))) AS S0
	                                    LEFT JOIN
	                                    (SELECT b.BookID, SUM(bb.Copy) AS BillSold
	                                    FROM Book b, Bill_Book bb, Bill bi
	                                    WHERE b.BookID = bb.BookID
		                                    AND bi.BillID = bb.BillID
		                                    AND (bi.ProjectID = @projectID OR @projectID = 0)
		                                    AND (b.BookID IN
		                                    (SELECT DISTINCT pb.BookID 
		                                    FROM Project_Book pb
		                                    WHERE (pb.ProjectID = @projectID OR @projectID = 0)))
	                                    GROUP BY b.BookID) AS S1 ON S0.BookID = S1.BookID
	                                    LEFT JOIN
	                                    (SELECT b.BookID, SUM(ob.Copy) AS OrderSold
	                                    FROM Book b, Order_Book ob, [Order] o
	                                    WHERE b.BookID = ob.BookID
		                                    AND o.OrderID = ob.OrderID
		                                    AND (o.ProjectID = @projectID OR @projectID = 0)
		                                    AND (b.BookID IN
		                                    (SELECT DISTINCT pb.BookID 
		                                    FROM Project_Book pb
		                                    WHERE (pb.ProjectID = @projectID OR @projectID = 0)))
	                                    GROUP BY b.BookID) AS S2 ON S2.BookID = S0.BookID) AS bookSold, Book _b
	                                    WHERE _b.BookID =b.BookID
		                                    AND b.BookID = bookSold.BookID
		                                    AND b.BookID = bookSold.BookID
                                            AND (_b.Price >= @lowPrice OR @lowPrice = 0)
                                            AND (_b.Price <= @highPrice OR @highPrice = 0) 
                                            AND (b.Authors LIKE @author OR @author = '%%') 
                                            AND (CAST(_b.ISBN AS nvarchar(20)) LIKE @isbn OR @isbn = '0%') 
                                            AND (_b.Publisher = @publisher OR @publisher = '' )
                                            AND (_b.Title LIKE @title OR @title = '%%' )
                                            AND (_b.Issuer = @issuer OR @issuer = '' )
                                            AND (_b.Genre = @genre OR @genre = '' )";

                 if (orderBy != "")
                 {                     
                     switch (orderBy.ToLower())
                     { 
                         case "price":
                             sqlString += "ORDER BY _b.Price";
                             break;
                         case "sold":
                             sqlString += "ORDER BY bookSold.Sold";
                             break;
                         case "title":
                             sqlString += "ORDER BY _b.Title";
                             break;                     
                     }
                     if (desc)
                     {
                         sqlString += " DESC";
                     }

                 }

                 DB.SetQuery(sqlString);
                 DB.AddParameters("isbn", isbn.ToString()+"%");
                 DB.AddParameters("lowPrice", lowPrice);
                 DB.AddParameters("highPrice", highPrice);
                 DB.AddParameters("author", "%"+author+"%");
                 DB.AddParameters("issuer", issuer);
                 DB.AddParameters("title", "%" + title + "%");
                 DB.AddParameters("publisher", publisher);
                 DB.AddParameters("genre", genre);
                 DB.AddParameters("projectID", pID);

                 List<Book_FilterView> result = new List<Book_FilterView>();
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 if (lol.Count > 0)
                 {
                     foreach (List<object> lo in lol)
                     {//_b.BookID,_b.ISBN,_b.Title,_b.Type,_b.Publisher,_b.Issuer,_b.Size,_b.Page,_b.Price,_b.Genre,_b.Weigh, b.Authors,bookSold.Sold
                         Book b = new Book();
                         b.BookID = Convert.ToInt16(lo[0]);
                         b.ISBN = Convert.ToInt64(lo[1]);
                         b.Title = Convert.ToString(lo[2]);
                         b.Type = Convert.ToString(lo[3]);
                         b.Publisher = Convert.ToString(lo[4]);
                         b.Issuer = Convert.ToString(lo[5]);
                         if (lo[6] == null)
                         {
                             b.Size = "";
                         }
                         else
                         {
                             b.Size = Convert.ToString(lo[6]);
                         }
                         if (lo[7] == System.DBNull.Value)
                         {
                             b.Page = "";
                         }
                         else
                         {
                             b.Page = Convert.ToString(lo[7]);
                         }
                         b.Price = Convert.ToDouble(lo[8]);
                         if (lo[9] == System.DBNull.Value)
                         {
                             b.Genre = "";
                         }
                         else
                         {
                             b.Genre = lo[9].ToString();
                         }
                         if (lo[10] == System.DBNull.Value)
                         {
                             b.Weigh = 0;
                         }
                         else
                         {
                             b.Weigh = Convert.ToDouble(lo[10]);
                         }
                         string authors = "";
                         if (lo[11].ToString().Contains(" , "))
                         {
                             authors = "N/A";
                         }
                         else
                         {
                             authors = lo[11].ToString();
                         }
                         int sold = Convert.ToInt32(lo[12]);
                         result.Add(new Book_FilterView(new Book_Filter(b, authors, sold)));
                     }
                 }
                 else
                     "".Substring(2);
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllBookByFilter", e.ToString());
                 return null;

             }
         }



         public bool DeleteBook(int b)
         {
             DB.SetQuery("DELETE FROM Book WHERE BookID=@id");
             DB.AddParameters("id", b);
             try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.DeleteBook", e.ToString());
                 return false;
             }
         }

         public List<string> GetAllPublisher()
         {
             try
             {
                 DB.SetQuery("SELECT Publisher FROM dbo.Book GROUP BY Publisher");
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<string> result = new List<string>();
                 if (llo != null)
                 {
                     foreach (List<object> lo in llo)
                     {
                         result.Add(Convert.ToString(lo[0]));
                     }
                 }
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllPublisher", e.ToString());
                 return null;
             }
         }

         public List<string> GetAllIssuer()
         {
             try
             {
                 DB.SetQuery("SELECT Issuer FROM dbo.Book GROUP BY Issuer");
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<string> result = new List<string>();
                 if (llo != null)
                 {
                     foreach (List<object> lo in llo)
                     {
                         result.Add(Convert.ToString(lo[0]));
                     }
                 }
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllIssuer", e.ToString());
                 return null;
             }
         }

         public List<string> GetAllGenre()
         {
             try
             {
                 DB.SetQuery("SELECT Genre FROM dbo.Book GROUP BY Genre");
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<string> result = new List<string>();
                 if (llo != null)
                 {
                     foreach (List<object> lo in llo)
                     {
                         result.Add(Convert.ToString(lo[0]));
                     }
                 }
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllGenre", e.ToString());
                 return null;
             }
         }


         public Book GetBookByISBN(Int64 ISBN)
         {
             try
             {
                 DB.SetQuery("SELECT  BookID, ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Book WHERE ISBN = @ISBN");
                 DB.AddParameters("ISBN", ISBN);
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 Book b = new Book();
                 if (llo.Count > 0)
                 {
                     b.BookID = Convert.ToInt16(llo[0][0]);
                     b.ISBN = Convert.ToInt64(llo[0][1]);
                     b.Title = Convert.ToString(llo[0][2]);
                     b.Type = Convert.ToString(llo[0][3]);
                     b.Publisher = Convert.ToString(llo[0][4]);
                     b.Issuer = Convert.ToString(llo[0][5]);
                     if (llo[0][6] == null)
                     {
                         b.Size = "";
                     }
                     else
                     {
                         b.Size = Convert.ToString(llo[0][6]);
                     }
                     if (llo[0][7] == System.DBNull.Value)
                     {
                         b.Page = "";
                     }
                     else
                     {
                         b.Page = Convert.ToString(llo[0][7]);
                     }
                     if (llo[0][8] == null)
                     {
                         b.Genre = "";
                     }
                     else
                     {
                         b.Genre = llo[0][8].ToString();
                     }
                     b.Price = Convert.ToDouble(llo[0][9]);
                     if (llo[0][10] == System.DBNull.Value)
                     {
                         b.Weigh = 0;
                     }
                     else
                     {
                         b.Weigh = Convert.ToDouble(llo[0][10]);
                     }
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return b;

             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetBookByISBN", e.ToString());
                 return null;

             }         
         }

         public Book GetBookById(int id)
         {
             try
             {
                 DB.SetQuery("SELECT  BookID, ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Book WHERE BookID=@id");
                 DB.AddParameters("id", id);
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 Book b = new Book();
                 if (llo.Count > 0)
                 {
                     b.BookID = Convert.ToInt16(llo[0][0]);
                     b.ISBN = Convert.ToInt64(llo[0][1]);
                     b.Title = Convert.ToString(llo[0][2]);
                     b.Type = Convert.ToString(llo[0][3]);
                     b.Publisher = Convert.ToString(llo[0][4]);
                     b.Issuer = Convert.ToString(llo[0][5]);
                     if (llo[0][6] == null)
                     {
                         b.Size = "";
                     }
                     else
                     {
                         b.Size = Convert.ToString(llo[0][6]);
                     }
                     if (llo[0][7] == System.DBNull.Value)
                     {
                         b.Page = "";
                     }
                     else
                     {
                         b.Page = Convert.ToString(llo[0][7]);
                     }
                     if (llo[0][8] == null)
                     {
                         b.Genre = "";
                     }
                     else
                     {
                         b.Genre = llo[0][8].ToString();
                     }
                     b.Price = Convert.ToDouble(llo[0][9]);
                     if (llo[0][10] == System.DBNull.Value)
                     {
                         b.Weigh = 0;
                     }
                     else
                     {
                         b.Weigh = Convert.ToDouble(llo[0][10]);
                     }
                 }
                 return b;

             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetBookByID", e.ToString());
                 return null;

             }
         }


         public List<Book> GetAllBook()
         {
             try
             {
                 DB.SetQuery("SELECT  BookID, ISBN,Title,Type,Publisher,Issuer,ISNULL(Size,'N/A'),ISNULL(Page,'N/A'),ISNULL(Genre,'N/A'),Price,ISNULL(Weigh,0) FROM Book");
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<Book> lb = new List<Book>();
                 Book b;
                 if (llo.Count > 0)
                 {
                     foreach (List<object> lo in llo)
                     {
                         b = new Book();
                         b.BookID = Convert.ToInt16(lo[0]);
                         b.ISBN = Convert.ToInt64(lo[1]);
                         b.Title = Convert.ToString(lo[2]);
                         b.Type = Convert.ToString(lo[3]);
                         b.Publisher = Convert.ToString(lo[4]);
                         b.Issuer = Convert.ToString(lo[5]);
                         if (lo[6] == null)
                         {
                             b.Size = "";
                         }
                         else
                         {
                             b.Size = Convert.ToString(lo[6]);
                         }
                         if (lo[7] == System.DBNull.Value)
                         {
                             b.Page = "";
                         }
                         else
                         {
                             b.Page = Convert.ToString(lo[7]);
                         }
                         if (lo[8] == null)
                         {
                             b.Genre = "";
                         }
                         else
                         {
                             b.Genre = lo[8].ToString();
                         }
                         b.Price = Convert.ToDouble(lo[9]);
                         if (lo[10] == System.DBNull.Value)
                         {
                             b.Weigh = 0;
                         }
                         else
                         {
                             b.Weigh = Convert.ToDouble(lo[10]);
                         }
                         lb.Add(b);
                     }
                 }
                 return lb;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllBook", e.ToString());
                 return null;
             }

         }

         public List<Book> GetAllBookByTitle(string title)
         {
             try
             {
                 DB.SetQuery("SELECT  BookID, ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Book WHERE Title LIKE @title");
                 DB.AddParameters("title", "%" + title + "%");
                 List<List<object>> llo = new List<List<object>>();
                 llo = DB.ExeQuery();
                 List<Book> lb = new List<Book>();
                 Book b;
                 if (llo.Count > 0)
                 {
                     foreach (List<object> lo in llo)
                     {
                         b = new Book();
                         b.BookID = Convert.ToInt16(lo[0]);
                         b.ISBN = Convert.ToInt64(lo[1]);
                         b.Title = Convert.ToString(lo[2]);
                         b.Type = Convert.ToString(lo[3]);
                         b.Publisher = Convert.ToString(lo[4]);
                         b.Issuer = Convert.ToString(lo[5]);
                         if (lo[6] == null)
                         {
                             b.Size = "";
                         }
                         else
                         {
                             b.Size = Convert.ToString(lo[6]);
                         }
                         if (lo[7] == System.DBNull.Value)
                         {
                             b.Page = "";
                         }
                         else
                         {
                             b.Page = Convert.ToString(lo[7]);
                         }
                         if (lo[8] == null)
                         {
                             b.Genre = "";
                         }
                         else
                         {
                             b.Genre = lo[8].ToString();
                         }
                         b.Price = Convert.ToDouble(lo[9]);
                         if (lo[10] == System.DBNull.Value)
                         {
                             b.Weigh = 0;
                         }
                         else
                         {
                             b.Weigh = Convert.ToDouble(lo[10]);
                         }
                         lb.Add(b);
                     }
                 }
                 return lb;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllBook", e.ToString());
                 return null;
             }

         }


        
        
    }
}
